using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using System.Data;
using System.Data.Common;
using NCT.Framework.Database;

namespace VISS.API.Users {
	class UserLivingProfileDA {

        private const string SP_GetAll = "dbo.UserLivingProfile_GetAll";
		private const string SP_GetByUserKey = "dbo.UserLivingProfile_GetByUserKey";
		private const string SP_Insert = "dbo.UserLivingProfile_Insert";
		private const string SP_Update = "dbo.UserLivingProfile_Update";

		public static UserLivingProfileEnt CreateFromReader(IDataReader reader) {
			UserLivingProfileEnt item = new UserLivingProfileEnt();
			item.UserKey = ConvertUtils.ToString(reader["UserKey"]);
			item.DrinkingID = ConvertUtils.ToShort(reader["DrinkingID"]);
			item.Drinking = ConvertUtils.ToString(reader["Drinking"]);
			item.SmokingID = ConvertUtils.ToShort(reader["SmokingID"]);
			item.Smoking = ConvertUtils.ToString(reader["Smoking"]);
			item.PetIDs = ConvertUtils.ToString(reader["PetIDs"]);
			//item.Pets = ConvertUtils.ToString(reader["Pets"]);
			item.Income = ConvertUtils.ToInt(reader["Income"]);
			item.HouseID = ConvertUtils.ToShort(reader["HouseID"]);
			item.House = ConvertUtils.ToString(reader["House"]);
			item.LivingStatusID = ConvertUtils.ToShort(reader["LivingStatusID"]);
			item.LivingStatus = ConvertUtils.ToString(reader["LivingStatus"]);
			item.MovingID = ConvertUtils.ToShort(reader["MovingID"]);
			item.Moving = ConvertUtils.ToString(reader["Moving"]);
			item.DateCreated = ConvertUtils.ToDateTime(reader["DateCreated"]);
			item.DateModified = ConvertUtils.ToDateTime(reader["DateModified"]);
			return item;
		}

        public static List<UserLivingProfileEnt> GetAll(string userKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserLivingProfileEnt> result = new List<UserLivingProfileEnt>();
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetAll)) {
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }

        public static UserLivingProfileEnt GetByUserKey(string userKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			UserLivingProfileEnt result = null;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetByUserKey)) {
				db.AddParameter(cmd, "UserKey", userKey);
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					if (reader.Read()) {
						result = CreateFromReader(reader);
					}
				}
			}
			return result;
		}

		public static int Insert(UserLivingProfileEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_Insert)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "DrinkingID", item.DrinkingID);
				db.AddParameter(cmd, "SmokingID", item.SmokingID);
				db.AddParameter(cmd, "PetIDs", item.PetIDs);
				db.AddParameter(cmd, "Income", item.Income);
				db.AddParameter(cmd, "HouseID", item.HouseID);
				db.AddParameter(cmd, "LivingStatusID", item.LivingStatusID);
				db.AddParameter(cmd, "MovingID", item.MovingID);
				db.AddParameter(cmd, "DateCreated", item.DateCreated);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int Update(UserLivingProfileEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_Update)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "DrinkingID", item.DrinkingID);
				db.AddParameter(cmd, "SmokingID", item.SmokingID);
				db.AddParameter(cmd, "PetIDs", item.PetIDs);
				db.AddParameter(cmd, "Income", item.Income);
				db.AddParameter(cmd, "HouseID", item.HouseID);
				db.AddParameter(cmd, "LivingStatusID", item.LivingStatusID);
				db.AddParameter(cmd, "MovingID", item.MovingID);
				db.AddParameter(cmd, "DateCreated", item.DateCreated);
				db.AddParameter(cmd, "DateModified", item.DateModified);
				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

	}
}